package davidlauzon.activerecord.visitor;

import java.util.Map;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import davidlauzon.activerecord.nodes.AliasableColumnIF;
import davidlauzon.activerecord.nodes.AliasableTableIF;
import davidlauzon.activerecord.nodes.Count;
import davidlauzon.activerecord.nodes.CreateTableStatement;
import davidlauzon.activerecord.nodes.DeleteStatement;
import davidlauzon.activerecord.nodes.DropTableStatement;
import davidlauzon.activerecord.nodes.InsertStatement;
import davidlauzon.activerecord.nodes.Node;
import davidlauzon.activerecord.nodes.SelectStatement;
import davidlauzon.activerecord.nodes.SortableColumnIF;
import davidlauzon.activerecord.nodes.Table;
import davidlauzon.activerecord.nodes.UpdateStatement;
import davidlauzon.activerecord.nodes.WhereClause;
import davidlauzon.activerecord.nodes.field.Field;
import davidlauzon.activerecord.nodes.field.FloatField;
import davidlauzon.activerecord.nodes.field.IntegerField;
import davidlauzon.activerecord.nodes.field.StringField;


public abstract class SqlSerializer
{
    /*********************************************************************************************
     * CONSTANTS
     *********************************************************************************************/
    static private boolean LOGGABLE = false;
    static private final Pattern PATTERN_WHERE_VALUE = Pattern.compile("\\?");
    
    
    /*********************************************************************************************
     * VARIABLES
     *********************************************************************************************/
    static protected Matcher _whereValueMatcher;
    
    
    
    public String visit(Vector<Node> nodes) {
        log("visit", "Vector<Node>");
        StringBuilder builder = new StringBuilder();
        
        for (Node node : nodes)
            builder.append(visit(node));
  
        return builder.toString();
    }
    
    public String visit(InsertStatement node)
    {
        log("visit", "InsertStatement");
        StringBuilder builder = new StringBuilder();
        
        builder.append("INSERT INTO " + visit(node.into()) );
        
        Field[] columns = node.columns();
        if (columns != null) {
            int size = columns.length;
            if (size > 0)
            {
                builder.append(" (" + visit(columns[0]) );
                
                for (int i = 1 ; i < size; i++)
                    builder.append( "," + visit(columns[i]) );
                
                builder.append(")");
            }
        }
        
        Field field = null;
        Object[] values = node.values();
        if (values != null) {
            int size = values.length;
            if (size > 0)
            {
                field = (columns != null) ? columns[0] : null;
                builder.append(" VALUES (" + visitFieldValue(field, values[0]) );
                
                for (int i = 1 ; i < size; i++) {
                    field = (columns != null) ? columns[i] : null;
                    builder.append( "," + visitFieldValue(field, values[i]) );
                }
                
                builder.append(")");
            }
        }
        
        return builder.toString();
    }
    
    public String visit(UpdateStatement node)
    {
        log("visit", "UpdateStatement");
        StringBuilder builder = new StringBuilder();
        
        builder.append("UPDATE " + visit(node.getTable()) + " SET " );
        
        Map<Field, Object> values = node.getValues();
        if ( values != null) {
            int size = values.size();
            int i = 0;
            
            for( Map.Entry<Field, Object> entry : values.entrySet() )
            {
                i++;
                builder.append( visitKeyValuePairForUpdate( entry.getKey(), entry.getValue() ) );
                if (i < size)
                    builder.append( ", " );
            }
        }
        
        WhereClause where = node.getWhereClause();
        if (where != null) {
            builder.append( visitWhereClause( where ) );
        }
                
        return builder.toString();
    }
    
    public String visit(SelectStatement node)
    {
        log("visit", "SelectStatement");
        StringBuilder builder = new StringBuilder();
        
        int i;
        int size;
        
        // SELECT COLUMNS
        builder.append("SELECT ");
        
        if (node.isDistinct())
            builder.append("DISTINCT ");
        
        AliasableColumnIF[] columns = node.selectColumns();
        if ( node.isAggregateFunction() )
        {
            builder.append( visit(node.aggregateFunction()) );
        } else {
            if ( columns == null || columns.length == 0 ) {
                builder.append("*");
            } else {
                size = columns.length;
                for (i = 0 ; i < size ; i++) {
                    if (i < size - 1)
                        builder.append( visit(columns[i]) + ", " );
                    else
                        builder.append( visit(columns[i]) );
                }
            }
        }
        
        // SELECT TABLES
        builder.append(" FROM ");
        AliasableTableIF[] tables = node.selectTables();
        if (tables == null) {
            throw new IllegalArgumentException("Must specify at least one table.");
        } else {
            size = tables.length;
            for (i = 0 ; i < size ; i++) {
                if (i < size - 1)
                    builder.append( visit(tables[i]) + ", " );
                else
                    builder.append( visit(tables[i]) );
            }
        }
        
        // WHERE
        WhereClause where = node.whereClause();
        if (where != null) {
            builder.append( visitWhereClause( where ) );
        }
        
        // GROUP BY
        AliasableColumnIF[] groupColumns = node.groupColumns();
        if (groupColumns != null && groupColumns.length > 0)
            builder.append( visitGroupByClause(groupColumns) );
        
        // ORDER BY
        SortableColumnIF[] orderColumns = node.orderColumns();
        if (orderColumns != null && orderColumns.length > 0)
            builder.append( visitOrderByClause(orderColumns) );
        
        // LIMIT
        if (node.limit() > 0)
            builder.append( visitLimit(node.limit()) );
        
        // OFFSET
        if (node.offset() > 0)
            builder.append( visitOffset(node.offset()) );
        
        return builder.toString();
    }
    
    public String visit(Count node) {
        return "COUNT(*)";
    }
    
    public String visitGroupByClause( AliasableColumnIF[] columns ) {
        return " GROUP BY " + joinArray(columns, ',' , 0, columns.length);
    }
    
    public String visitOrderByClause( SortableColumnIF[] columns ) {
        return " ORDER BY " + joinArray(columns, ',' , 0, columns.length);
    }
    
    public String visitLimit( int limit ) {
        return " LIMIT " + limit;
    }
    
    public String visitOffset( long offset ) {
        return " OFFSET " + offset;
    }
    
    public String visit(DeleteStatement node)
    {
        log("visit", "DeleteStatement");
        StringBuilder builder = new StringBuilder();
        
        builder.append("DELETE FROM " + visit(node.getFrom()) );
            
        WhereClause where = node.getWhereClause();
        if (where != null) {
            builder.append( visitWhereClause( where ) );
        }
                
        return builder.toString();
    }
    
    public String visit(DropTableStatement node)
    {
        log("visit", "DropTableStatement");
        StringBuilder builder = new StringBuilder();
        
        builder.append("DROP TABLE ");
        
        if (node.ifExists())
            builder.append("IF EXISTS ");
        
        builder.append( visit(node.table()) );
                
        return builder.toString();
    }
    
    public String visit(CreateTableStatement node)
    {
        log("visit", "CreateTableStatement");
        StringBuilder builder = new StringBuilder();
        
        builder.append("CREATE TABLE ");
        
        if (node.ifNotExists())
            builder.append("IF NOT EXISTS ");
        
        builder.append( visit(node.table()) + " (" );
        
        Field[] columns = node.columns();
        if ( columns != null) {
            int size = columns.length;
            int i;
            
            for( i = 0 ; i < size ; i++ )
            {
                builder.append( visitColumnDefinition( columns[i] ) );
                if (i + 1 < size)
                    builder.append( ", " );
            }
        }
        builder.append( visitTableConstraints(node.table()) + " )");
        
        return builder.toString();
    }
    
    public String visitWhereClause(WhereClause node)
    {
        log("visit", "WhereClause");

        StringBuilder builder = new StringBuilder();
        
        String condition = node.getCondition();
        if (condition != null && condition.length() > 0)
        {
            builder.append(" WHERE ");
            Object[] unsafeValues = node.getUnsafeValues();
            
            if (unsafeValues != null) {
                int counter = 0;
                int size    = unsafeValues.length;
                
                if (_whereValueMatcher == null)
                    _whereValueMatcher = PATTERN_WHERE_VALUE.matcher( condition );
                else
                    _whereValueMatcher.reset( condition );
                
                while ( counter < size ) {
                    condition = _whereValueMatcher.replaceFirst( sanitize(unsafeValues[counter].toString() ));
                    _whereValueMatcher.reset( condition );
                    counter++;
                }
            }
            builder.append(condition);
        }
        
        return builder.toString();
    }

    
    private String visitKeyValuePairForUpdate(Field field, Object value)
    {
        return visit( field ) + "=" + visitFieldValue(field, value);
    }

    /**
     * FIXME: there must be a better way to detect the field's absolute class!!
     */
    public String visitFieldValue( Field field, Object value ) {
        log("visitFieldValue", "Field, Object");
        if (field instanceof StringField)
            return visitFieldValue( (StringField) field , value);
        else if (field instanceof IntegerField)
            return visitFieldValue( (IntegerField) field , value);
        else if (field instanceof FloatField)
            return visitFieldValue( (FloatField) field , value);
        else if (field == null)
            throw new IllegalArgumentException("The method " + getClass().getSimpleName() +
                    "::visitFieldValue( field = null, Object value) does not exists.");
        else
            throw new IllegalArgumentException("The method " + getClass().getSimpleName() +
                    "::visitFieldValue(" + field.getClass().getSimpleName() + 
                    " field, Object value) does not exists.");
    }
    

    /**
     * CharSequence: CharBuffer, String, StringBuffer, StringBuilder
     */
    public String visitFieldValue(StringField field, Object value) {
        log("visitFieldValue", "StringField");
        return quote( value.toString() );
    }
    
    /**
     * Number: BigDecimal, BigInteger, Byte, Double, Float, Integer, Long, Short
     */
    public String visitFieldValue(IntegerField field, Object value) {
        log("visitFieldValue", "IntegerField");
        return String.valueOf( IntegerField.parseValue(value) );
    }
    
    public String visitFieldValue(FloatField field, Object value) {
        log("visitFieldValue", "FloatField");
        return String.valueOf( FloatField.parseValue(value) );
    }
    
    public String visitColumnDefinition( Field field ) {
        log("visitColumnDefinition", "Field");
        if (field instanceof StringField)
            return visitColumnDefinition( (StringField) field );
        else if (field instanceof IntegerField)
            return visitColumnDefinition( (IntegerField) field );
        else if (field instanceof FloatField)
            return visitColumnDefinition( (FloatField) field );
        else if (field == null)
            throw new IllegalArgumentException("The method " + getClass().getSimpleName() +
                    "::visitColumnDefinition( field = null, Object value) does not exists.");
        else
            throw new IllegalArgumentException("The method " + getClass().getSimpleName() +
                    "::visitColumnDefinition(" + field.getClass().getSimpleName() + 
                    " field, Object value) does not exists.");
    }
    
    public String visitColumnDefinition( StringField field ) {
        log("visitColumnDefinition", "StringField");
        return quote_column_name(field.name()) + " VARCHAR(255) NULL";
    }
    
    public String visitColumnDefinition( IntegerField field ) {
        log("visitColumnDefinition", "IntegerField");
        if (field.isPrimaryKey())
            return quote_column_name(field.name()) + " INTEGER PRIMARY KEY AUTOINCREMENT";
        else
            return quote_column_name(field.name()) + " INT NULL";
    }
    
    public String visitColumnDefinition( FloatField field ) {
        log("visitColumnDefinition", "FloatField");
        return quote_column_name(field.name()) + " FLOAT NULL";
    }
    
    public String visitTableConstraints( Table table ) {
        log("visitTableConstraints", "Table");
        return "";
    }
    

    public String visit(AliasableTableIF table) {
        log("visit", "AliasableTable");
        return quote_table_name( table.name() );
    }
    
    public String visit(Table table) {
        log("visit", "Table");
        return quote_table_name( table.name() );
    }

    public String visit(Field field) {
        log("visit", "Field");
        StringBuilder builder = new StringBuilder();
        
        builder.append( quote_column_name(field.name()) );
        
        if (field.alias() != null)
            builder.append(" AS " + quote_column_name(field.alias()) );
        
        if (field.sortDirection() != null)       // SHOULD NOT have both an alias AND a sort
            builder.append(" " + field.sortDirection() );
        
        return builder.toString();
    }
    
    /**
     * CharBuffer, String, StringBuffer, StringBuilder
     */
    public String visit(CharSequence node) {
        log("visit", "CharSequence");
        return quote( node.toString() );
    }
    
    /**
     * BigDecimal, BigInteger, Byte, Double, Float, Integer, Long, Short
     */
    public String visit(Number node) {
        log("visit", "Number");
        return node.toString();
    }
    
    public String visit(Object node) {
        log("visit", "Object");
            
        if (node instanceof Field)
            return visit( (Field) node);
        else if (node instanceof Count)
            return visit( (Count) node);
        else
            throw new IllegalArgumentException("The method " + getClass().getSimpleName() +
                    "::visit(" + node.getClass().getSimpleName() + " node) does not exists.");
    }
    
    public String quote( String value ) {
        return "'" + sanitize(value)  + "'";
    }
    
    public String quote_column_name( String name ) {
        return "\"" + name + "\"";
    }
    
    public String quote_table_name( String name ) {
        return "\"" + name + "\"";
    }
    
    public String sanitize( String unsafeValue ) {
        return unsafeValue.replaceAll("'", "''");
    }
    
    protected static void log(String funcName, Object message) {
        if (LOGGABLE)
            System.out.println("::" + funcName + "() " + message);
    }
    
    
    public String joinArray(Object[] array, char separator, int startIndex, int endIndex) {
        if (array == null) {
            return null;
        }
        int bufSize = (endIndex - startIndex);
        if (bufSize <= 0) {
            return null;
        }

        bufSize *= ((array[startIndex] == null ? 16 : array[startIndex].toString().length()) + 1);
        StringBuilder buf = new StringBuilder(bufSize);

        for (int i = startIndex; i < endIndex; i++) {
            if (i > startIndex) {
                buf.append(separator);
            }
            if (array[i] != null) {
                buf.append(array[i]);
            }
        }
        return buf.toString();
    }
}
